import pymysql

db = pymysql.connect(host='localhost', user='root', password='123456', database="test")
cursor = db.cursor()
cursor.execute('DROP TABLE IF EXISTS employee')
sql = """CREATE TABLE`employee`(
    `id`int(10) NOT NULL AUTO_INCREMENT,
    `first_name`char(20) NOT NULL,
    `last_name`char(20) DEFAULT NULL,
    `age`int(11)DEFAULT NULL,
    `sex`char(1)DEFAULT NULL,
    `income`float DEFAULT NULL,
    PRIMARY KEY(`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"""
cursor.execute(sql)
print("Created table Successfull.")
db.close()

db = pymysql.connect(host='localhost', user='root', password='123456', database="test")
cursor=db.cursor()
sql = """
INSERT INTO
  EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
VALUES
  ('Mac','Su','20','M',5000)
"""
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()

